﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Configuration;
using Zxb.DayWork.Core;

namespace Zxb.DayWork.Logic.del
{
    public class EstateManager
    {
        #region 新增楼盘的导入

        //public static void AutoImport(NorthSouth ns)
        //{
        //    int startid = DAL.GetLastID(Constant.DicNorthSouth[ns].StatusName);
        //    int endid = startid + 1;
        //    while (startid < endid)
        //    {
        //        startid = endid;
        //        DataTable dt = PrepareDTFromNewHouse(ns, startid, 1000);
        //        endid = TVPInsert(dt, ns);
        //        Console.WriteLine(ns.ToString() + endid.ToString());
        //        Zxb.DayWork.Logic.Logger.InfoLog.Info(ns.ToString() + endid.ToString());
        //    }
        //}

        //public static DataTable PrepareDTFromNewHouse(NorthSouth ns, int startid, int num)
        //{
        //    DataTable targetTable = PrepareTableSchemaUdtEstateManage();

        //    DataTable dt = null;
        //    Database sourcedb = Constant.DicNorthSouth[ns].DataBase;
        //    string sql = string.Format("select top {0} NID,Newcode,ProjName,proj_type,Province,City,District,ComArea,Address,PartTotalDoor,TotalDoor,LivinDate,PurposeArea,BuildCategory,FixStatus from proj_info with(nolock) where NID>{1} order by NID", num, startid);
        //    DbCommand dbCommand = sourcedb.GetSqlStringCommand(sql);
        //    dt = sourcedb.ExecuteDataSet(dbCommand).Tables[0];
        //    foreach (DataRow dr in dt.Rows)
        //    {
        //        long estID = 0;
        //        long.TryParse(dr["Newcode"].ToString(), out estID);
        //        DateTime acceptTime = new DateTime(1901, 1, 1);
        //        DateTime.TryParse(dr["LivinDate"].ToString(), out acceptTime);
        //        int nid = 0;
        //        int.TryParse(dr["NID"].ToString(), out nid);
        //        int acceptuser = 0;
        //        int.TryParse(dr["PartTotalDoor"].ToString(), out acceptuser);
        //        int totaluser = 0;
        //        int.TryParse(dr["TotalDoor"].ToString(), out totaluser);
        //        decimal estArea = 0.0M;
        //        decimal.TryParse(dr["PurposeArea"].ToString(), out estArea);

        //        DataRow targetRow = targetTable.NewRow();
        //        targetRow["RealEstateID"] = estID;
        //        targetRow["RealEstateName"] = dr["ProjName"].ToString();
        //        targetRow["NewHouseRealEstateID"] = nid;
        //        targetRow["NorthSouth"] = ns.ToString();

        //        targetRow["ProjType"] = dr["Proj_Type"].ToString();
        //        targetRow["Province"] = dr["Province"].ToString();
        //        targetRow["City"] = dr["City"].ToString();
        //        targetRow["District"] = dr["District"].ToString();
        //        targetRow["ComArea"] = dr["ComArea"].ToString();
        //        targetRow["Addr"] = dr["Address"].ToString();

        //        targetRow["AcceptUserNumber"] = acceptuser;
        //        targetRow["TotalUserNumber"] = totaluser;
        //        targetRow["AcceptTime"] = acceptTime;
        //        targetRow["PurposeArea"] = estArea;
        //        targetRow["BuildCategory"] = dr["BuildCategory"].ToString();
        //        targetRow["FixStatus"] = dr["FixStatus"].ToString();
        //        //targetRow["CaseAllHits"] = 0L;
        //        //targetRow["CaseListHits"] = 0L;

        //        targetTable.Rows.Add(targetRow);
        //    }
        //    return targetTable;
        //}

        //public static DataTable PrepareTableSchemaUdtEstateManage()
        //{
        //    DataTable dt = new DataTable();
        //    dt.Columns.AddRange(new DataColumn[]
        //    { 
        //        new DataColumn("RealEstateID",typeof(Int64)), 
        //        new DataColumn("RealEstateName",typeof(string)), 
        //        new DataColumn("NewHouseRealEstateID",typeof(int)),
        //        new DataColumn("NorthSouth",typeof(string)), 

        //        new DataColumn("ProjType",typeof(string)), 
        //        new DataColumn("Province",typeof(string)), 
        //        new DataColumn("City",typeof(string)), 
        //        new DataColumn("District",typeof(string)), 
        //        new DataColumn("ComArea",typeof(string)), 
        //        new DataColumn("Addr",typeof(string)), 

        //        new DataColumn("AcceptUserNumber",typeof(Int32)),
        //        new DataColumn("TotalUserNumber",typeof(Int32)),
        //        new DataColumn("AcceptTime",typeof(DateTime)), 
        //        new DataColumn("PurposeArea",typeof(decimal)), 
        //        new DataColumn("BuildCategory",typeof(string)), 
        //        new DataColumn("FixStatus",typeof(string))
        //    });
        //    return dt;
        //}

        ////依赖dt的NewHouseRealEstateID排序
        //private static int TVPInsert(DataTable dt, NorthSouth ns)
        //{
        //    SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ZxbWriter"].ConnectionString);

        //    SqlCommand cmd = new SqlCommand("EstateManageImport", sqlConn);
        //    cmd.CommandType = CommandType.StoredProcedure;

        //    SqlParameter tvpParam = cmd.Parameters.AddWithValue("@tvpNew", dt);
        //    tvpParam.SqlDbType = SqlDbType.Structured;
        //    tvpParam.TypeName = "dbo.UdtEstateManage";
        //    SqlParameter statusParam = cmd.Parameters.AddWithValue("@StatusName", Constant.DicNorthSouth[ns].StatusName);
        //    statusParam.SqlDbType = SqlDbType.NVarChar;

        //    try
        //    {
        //        if (dt != null && dt.Rows.Count != 0)
        //        {
        //            sqlConn.Open();
        //            cmd.ExecuteNonQuery();
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        Logger.InfoLog.Error(string.Format("错误：{0}, 发生在新房库记录{1}与{2}之间 ", ex.Message, dt.Rows[0]["NewHouseRealEstateID"], dt.Rows[dt.Rows.Count-1]["NewHouseRealEstateID"]));
        //    }
        //    finally
        //    {
        //        sqlConn.Close();
        //    }

        //    return DAL.GetLastID(Constant.DicNorthSouth[ns].StatusName);
        //}

        #endregion

        #region 从新房 更新

        public static void AutoUpdateNewHouse()
        {

            //UpdatePrice(NorthSouth.North);
            //UpdatePrice(NorthSouth.South);

            //UpdateHits(NorthSouth.North);
            //UpdateHits(NorthSouth.South);
        }

        //public static void UpdatePrice(NorthSouth ns)
        //{
        //    int startid = GetLastID("proj_price每日更新楼盘平均价格,已处理的最大ID");
        //    int num = 100000;
        //    int maxid = ReadMaxIDFromAveragePrice(ns);
        //    for (int i = startid; i <= maxid; i += num)
        //    {
        //        DataTable dt = PrepareDtAveragePrice(ns, i, num);
        //        if (dt.Rows.Count == 0)
        //        {
        //            Logger.InfoLog.Info(string.Format("行数为0. 数据库:{0},maxid={1}", ns.ToString(), startid));
        //            break;
        //        }
        //        TVPUpdate(dt, "EstateManageUpdateAveragePrice");
        //    }

        //    //int startid = 0;
        //    //int num = 100000;
        //    //int endid = -1;
        //    //while (startid != endid)
        //    //{
        //    //    //处理循环条件
        //    //    endid = startid;
        //    //    DataTable dt = PrepareDtAveragePrice(ns, startid, num);
        //    //    if (dt.Rows.Count == 0)
        //    //    {
        //    //        Logger.InfoLog.Info(string.Format("已更新完毕AveragePrice. 数据库:{0},maxid={1}", ns.ToString(), startid));
        //    //        break;
        //    //    }
        //    //    int.TryParse(dt.Rows[dt.Rows.Count - 1]["RealEstateID"].ToString(), out startid);

        //    //    //更新平均价格
        //    //    TVPUpdate(dt, "EstateManageUpdateAveragePrice");
        //    //}
        //}

        //private static DataTable PrepareDtAveragePrice(NorthSouth ns, int startid, int num)
        //{
        //    DataTable targetTable = GetTableSchemaUdtEstateManageUpdate();

        //    DataTable dt = ReadDtAveragePrice(ns, startid, num);
        //    if (dt == null)
        //    {
        //        Logger.InfoLog.Error(string.Format("读取AveragePrice表时出错,数据库:{0},startid={1}", ns.ToString(), startid));
        //        return targetTable;
        //    }
        //    foreach (DataRow dr in dt.Rows)
        //    {
        //        decimal price = 0M;
        //        decimal.TryParse(dr["priceaverage"].ToString(), out price);
        //        long estid = 0L;
        //        long.TryParse(dr["newcode"].ToString(), out estid);
        //        DataRow targetRow = targetTable.NewRow();
        //        targetRow["RealEstateID"] = estid;
        //        targetRow["AveragePrice"] = price;
        //        targetRow["AveragePriceUnit"] = dr["priceaveragetype"].ToString();
        //        targetTable.Rows.Add(targetRow);
        //    }

        //    return targetTable;
        //}
        //private static DataTable ReadDtAveragePrice(NorthSouth ns, int startid, int num)
        //{
        //    DataTable dt = null;
        //    Database sourcedb = Constant.DicNorthSouth[ns].DataBase;
        //    //string sql = string.Format("select top {0} p.id,p.newcode,p.priceaverage,p.priceaveragetype from proj_price p with(nolock) inner join (select MAX(id) as mid from proj_price group by newcode) g on p.id=g.mid and p.id>{1} order by p.id", num, startid);
        //    string sql = string.Format("select p.id,p.newcode,p.priceaverage,p.priceaveragetype from proj_price p with(nolock) inner join (select newcode,MAX(id) as mid from proj_price with(nolock) where id>{0} and id<={1} group by newcode) g on p.id=g.mid order by p.id", startid, startid + num);
        //    DbCommand dbCommand = sourcedb.GetSqlStringCommand(sql);
        //    dt = sourcedb.ExecuteDataSet(dbCommand).Tables[0];
        //    return dt;
        //}
        //private static int ReadMaxIDFromAveragePrice(NorthSouth ns)
        //{
        //    Database sourcedb = Constant.DicNorthSouth[ns].DataBase;
        //    string sql = string.Format("select max(id) from proj_price with(nolock)");
        //    DbCommand dbCommand = sourcedb.GetSqlStringCommand(sql);
        //    int maxid = (int)sourcedb.ExecuteScalar(dbCommand);
        //    return maxid;
        //}

        //public static void UpdateHits(NorthSouth ns)
        //{
        //    int num = 10000;
        //    long maxNewcode = ReadMaxNewCode(ns);
        //    long startid = 0;
        //    while (startid < maxNewcode)
        //    {
        //        DataTable dt = PrepareDtEstateHits(ns, startid, num);
        //        if (dt.Rows.Count == 0)
        //        {
        //            Logger.InfoLog.Info(string.Format("行数为0. 数据库:{0},maxid={1}", ns.ToString(), startid));
        //            break;
        //        }
        //        TVPUpdate(dt, "EstateManageUpdateNewHouseHits");
        //        startid = (long)dt.Rows[dt.Rows.Count - 1]["RealEstateID"];
        //        Console.WriteLine("更新新房点击量. 数据库:{0},已完成最大newcode={1}", ns.ToString(), startid);
        //        //Logger.InfoLog.Info(string.Format("更新新房点击量. 数据库:{0},已完成最大newcode={1}", ns.ToString(), startid));
        //    }
        //}
        //private static DataTable PrepareDtEstateHits(NorthSouth ns, long startid, int num)
        //{
        //    DataTable targetTable = GetTableSchemaUdtEstateManageUpdate();

        //    DataTable dt = ReadDtEstateHits(ns, startid, num);
        //    if (dt == null)
        //    {
        //        Logger.InfoLog.Error(string.Format("ReadDtEstateHits时出错,数据库:{0},startid={1}", ns.ToString(), startid));
        //        return targetTable;
        //    }
        //    foreach (DataRow dr in dt.Rows)
        //    {
        //        long hits = 0L;
        //        long.TryParse(dr["hits"].ToString(), out hits);
        //        long estid = 0L;
        //        long.TryParse(dr["Newcode"].ToString(), out estid);
        //        DataRow targetRow = targetTable.NewRow();
        //        targetRow["RealEstateID"] = estid;
        //        targetRow["NewHouseHits"] = hits;
        //        targetTable.Rows.Add(targetRow);
        //    }

        //    return targetTable;
        //}
        //private static DataTable ReadDtEstateHits(NorthSouth ns, long startid, int num)
        //{
        //    string sql = string.Format("select top {0} Newcode,hits from proj_info with(nolock) where Newcode>{1} order by Newcode", num, startid);
        //    DataTable dt = DAL.ReadDtPage(ns, sql);
        //    return dt;
        //}
        //private static long ReadMaxNewCode(NorthSouth ns)
        //{
        //    Database sourcedb = Constant.DicNorthSouth[ns].DataBase;
        //    string sql = string.Format("select max(Newcode) from proj_info with(nolock)");
        //    DbCommand dbCommand = sourcedb.GetSqlStringCommand(sql);
        //    long maxNewcode = (long)sourcedb.ExecuteScalar(dbCommand);
        //    return maxNewcode;
        //}

        #endregion

        #region 从zxb更新

        public static void AutoUpdateZxb()
        {
            DataTable dt = PrepareDtCityID();
            DAL.TVPUpdate(dt, "EstateManageUpdateCityID", "dbo.UdtEstateManageUpdate");

            int endid = GetHitsEstateMaxID();
            dt = PrepareDtEstateManageUpZxbHits(endid);
            TVPUpdate(dt, "EstateManageUpZxbHits", "dbo.UdtEstateManageUpZxbHits", endid);
            

            //dt = GetUpdateTableCaseAllHits();
            //TVPUpdate(dt, "EstateManageUpdateCaseAllHits");

            //int startid = GetLastID("HitsWeiKe_Estate案例4S列表点击量统计位置,最大ID");
            //int endid = GetHitsEstateMaxID();
            //if (startid > endid || endid == -1)
            //{
            //    Logger.InfoLog.Error(string.Format("更新4S列表访问量出错,startid={0},endid={1}", startid, endid));
            //    return;
            //}

            //dt = GetUpdateTableCaseListHits(startid, endid);
            //TVPUpdate(dt, "EstateManageUpdateCaseListHits", endid);
        }

        private static void TVPUpdate(DataTable dt, string procedureName, string udtName, int maxid)
        {
            SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ZxbWriter"].ConnectionString);

            SqlCommand cmd = new SqlCommand(procedureName, sqlConn);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter tvpParam = cmd.Parameters.AddWithValue("@tvpNew", dt);
            tvpParam.SqlDbType = SqlDbType.Structured;
            tvpParam.TypeName = udtName;

            SqlParameter intParam = cmd.Parameters.AddWithValue("@maxid", maxid);
            intParam.SqlDbType = SqlDbType.Int;

            try
            {
                sqlConn.Open();
                if (dt != null && dt.Rows.Count != 0)
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.InfoLog.Error(string.Format("错误：{0}", ex.Message));
            }
            finally
            {
                sqlConn.Close();
            }
        }

        //private static void TVPUpdate(DataTable dt, string procedureName, string udtName)
        //{
        //    SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ZxbWriter"].ConnectionString);

        //    SqlCommand cmd = new SqlCommand(procedureName, sqlConn);
        //    cmd.CommandType = CommandType.StoredProcedure;

        //    SqlParameter tvpParam = cmd.Parameters.AddWithValue("@tvpNew", dt);
        //    tvpParam.SqlDbType = SqlDbType.Structured;
        //    tvpParam.TypeName = udtName;
        //    //tvpParam.TypeName = "dbo.UdtEstateManageUpdate";

        //    try
        //    {
        //        if (dt != null && dt.Rows.Count != 0)
        //        {
        //            sqlConn.Open();
        //            cmd.ExecuteNonQuery();
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        Logger.InfoLog.Error(string.Format("执行存储过程{1}时错误：{0}", ex.Message, procedureName));
        //    }
        //    finally
        //    {
        //        sqlConn.Close();
        //    }
        //}

        public static DataTable GetTableSchemaUdtEstateManageUpdate()
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[]
            { 
                new DataColumn("RealEstateID",typeof(long)),
                new DataColumn("NewHouseHits",typeof(Int64)),
                new DataColumn("NewHouseClicks",typeof(Int64)), 
                new DataColumn("City",typeof(string)), 
                new DataColumn("CityID",typeof(int)),
                new DataColumn("AveragePrice",typeof(decimal)),
                new DataColumn("AveragePriceUnit",typeof(string))
            });
            return dt;
        }
        

        private static DataTable PrepareDtCityID()
        {
            DataTable targetTable = GetTableSchemaUdtEstateManageUpdate();

            DataTable dt = ReadDtCity();
            foreach (DataRow dr in dt.Rows)
            {
                DataRow targetRow = targetTable.NewRow();
                targetRow["City"] = dr["CityName"].ToString();
                targetRow["CityID"] = (int)dr["id"];
                targetTable.Rows.Add(targetRow);
            }

            return targetTable;
        }
        private static DataTable ReadDtCity()
        {
            DataTable dt = null;
            Database sourcedb = DB.TaskReader;
            string sql = "select id,CityName from city";
            DbCommand dbCommand = sourcedb.GetSqlStringCommand(sql);
            dt = sourcedb.ExecuteDataSet(dbCommand).Tables[0];
            return dt;
        }

        //private static DataTable GetUpdateTableCaseAllHits()
        //{
        //    DataTable targetTable = GetTableSchemaUdtEstateManageUpdate();

        //    DataTable dt = ReadDtCaseAllHits();
        //    foreach (DataRow dr in dt.Rows)
        //    {
        //        long hitsum = 0L;
        //        long.TryParse(dr["hitsum"].ToString(), out hitsum);
        //        long estid = 0L;
        //        long.TryParse(dr["RealEstateID"].ToString(), out estid);
        //        DataRow targetRow = targetTable.NewRow();
        //        targetRow["RealEstateID"] = estid;
        //        targetRow["TotalNewCaseNum"] = hitsum;
        //        targetTable.Rows.Add(targetRow);
        //    }

        //    return targetTable;
        //}
        //private static DataTable ReadDtCaseAllHits()
        //{
        //    DataTable dt = null;
        //    Database sourcedb = DB.TaskReader;
        //    string sql = "select RealEstateID,SUM(hits) as hitsum from Cases with(nolock) where RealEstateID>10 group by RealEstateID";
        //    DbCommand dbCommand = sourcedb.GetSqlStringCommand(sql);
        //    dt = sourcedb.ExecuteDataSet(dbCommand).Tables[0];
        //    return dt;
        //}

        //private static DataTable GetUpdateTableCaseListHits(int startid, int endid)
        //{
        //    DataTable targetTable = GetTableSchemaUdtEstateManageUpdate();


        //    DataTable dt = ReadDtCaseListHits(startid, endid);
        //    foreach (DataRow dr in dt.Rows)
        //    {
        //        long hitsum = 0L;
        //        long.TryParse(dr["hitsum"].ToString(), out hitsum);
        //        long estid = 0L;
        //        long.TryParse(dr["EstateID"].ToString(), out estid);
        //        DataRow targetRow = targetTable.NewRow();
        //        targetRow["RealEstateID"] = estid;
        //        targetRow["CaseListHits"] = hitsum;
        //        targetTable.Rows.Add(targetRow);
        //    }

        //    return targetTable;
        //}
        //private static DataTable ReadDtCaseListHits(int startid, int endid)
        //{
        //    DataTable dt = null;
        //    Database db = DB.TaskReader;
        //    string sql = "select EstateID,COUNT(1) as hitsum from HitsWeiKe_Estate with(nolock) where ID>@startid and ID<=@endid group by EstateID";
        //    DbCommand dbCommand = db.GetSqlStringCommand(sql);
        //    db.AddInParameter(dbCommand, "@startid", DbType.String, startid);
        //    db.AddInParameter(dbCommand, "@endid", DbType.String, endid);
        //    dt = db.ExecuteDataSet(dbCommand).Tables[0];
        //    return dt;
        //}

        public static DataTable PrepareSchemaUdtEstateManageUpZxbHits()
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[]
            { 
                new DataColumn("RealEstateID",typeof(long)),
                new DataColumn("TotalNewCaseNum",typeof(int)),
                new DataColumn("TotalCaseListHits",typeof(int)),
                new DataColumn("TotalCaseInfoHits",typeof(int))
            });
            return dt;
        }
        private static DataTable PrepareDtEstateManageUpZxbHits(int endid)
        {
            DataTable targetTable = PrepareSchemaUdtEstateManageUpZxbHits();

            DataTable dt = ReadDt_CaseNum_CaseInfoHits();
            DataTable dtCaseListHits = ReadDt_CaseListHits(endid) ?? new DataTable();

            var query_CaseNum_CaseInfoHits_Join_CaseListHits
                = from e in dt.AsEnumerable()
                  join h in dtCaseListHits.AsEnumerable()
                  on e.Field<long>("RealEstateID") equals h.Field<long>("RealEstateID") into eh
                  from item in eh.DefaultIfEmpty()
                  select new
                  {
                      RealEstateID = e.Field<long>("RealEstateID"),
                      TotalNewCaseNum = e.Field<int>("TotalNewCaseNum"),
                      TotalCaseInfoHits = e.Field<int>("TotalCaseInfoHits"),
                      TotalCaseListHits = (item == null) ? 0 : item.Field<int>("TotalCaseListHits")
                  };
            foreach (var item in query_CaseNum_CaseInfoHits_Join_CaseListHits)
            {
                DataRow row = targetTable.NewRow();
                row["RealEstateID"] = item.RealEstateID;
                row["TotalNewCaseNum"] = item.TotalNewCaseNum;
                row["TotalCaseInfoHits"] = item.TotalCaseInfoHits;
                row["TotalCaseListHits"] = item.TotalCaseListHits;
                targetTable.Rows.Add(row);
            }

            return targetTable;
        }
        private static DataTable ReadDt_CaseNum_CaseInfoHits()
        {
            DataTable dt = null;
            Database sourcedb = DB.TaskReader;
            string sql = "select RealEstateID,COUNT(1) as TotalNewCaseNum,SUM(hits) as TotalCaseInfoHits from Cases with(nolock) where RealEstateID>10 group by RealEstateID";
            DbCommand dbCommand = sourcedb.GetSqlStringCommand(sql);
            dt = sourcedb.ExecuteDataSet(dbCommand).Tables[0];
            return dt;
        }
        private static DataTable ReadDt_CaseListHits(int endid)
        {
            int startid = DAL.GetLastID("HitsWeiKe_Estate案例4S列表点击量统计位置,最大ID");
            
            if (startid > endid || endid == -1)
            {
                Logger.InfoLog.Error(string.Format("更新案例列表访问量出错,startid={0},endid={1}", startid, endid));
                return null;
            }
            DataTable dt = ReadDt_CaseListHits(startid, endid);
            return dt;
        }
        private static DataTable ReadDt_CaseListHits(int startid, int endid)
        {
            DataTable dt = null;
            Database db = DB.TaskReader;
            string sql = "select EstateID as RealEstateID,COUNT(1) as TotalCaseListHits from HitsWeiKe_Estate with(nolock) where ID>@startid and ID<=@endid group by EstateID";
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            db.AddInParameter(dbCommand, "@startid", DbType.String, startid);
            db.AddInParameter(dbCommand, "@endid", DbType.String, endid);
            dt = db.ExecuteDataSet(dbCommand).Tables[0];
            return dt;
        }
        private static int GetHitsEstateMaxID()
        {
            int result = -1;
            Database db = DB.TaskReader;
            string sql = "select MAX(ID) from HitsWeiKe_Estate";
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            int.TryParse(db.ExecuteScalar(dbCommand).ToString(), out result);
            return result;
        }

        #endregion

        //public static void AutoUpdateStatEstate()
        //{
        //    DataTable dt = PrepareDt_EstateStat(DateTime.Now);
        //    TVPInsertEstateStat(dt);
        //}
        //public static void TVPInsertEstateStat(DataTable dt)
        //{
        //    SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ZxbWriter"].ConnectionString);

        //    SqlCommand cmd = new SqlCommand("EstateStatInsert", sqlConn);
        //    cmd.CommandType = CommandType.StoredProcedure;

        //    SqlParameter tvpParam = cmd.Parameters.AddWithValue("@tvpNew", dt);
        //    tvpParam.SqlDbType = SqlDbType.Structured;
        //    tvpParam.TypeName = "dbo.UdtEstateStat";

        //    try
        //    {
        //        if (dt != null && dt.Rows.Count != 0)
        //        {
        //            sqlConn.Open();
        //            cmd.ExecuteNonQuery();
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        Logger.InfoLog.Error(string.Format("错误：{0}, 发生在EstateStatInsert记录{1}与{2}之间 ", ex.Message, dt.Rows[0]["RealEstateID"], dt.Rows[dt.Rows.Count - 1]["RealEstateID"]));
        //    }
        //    finally
        //    {
        //        sqlConn.Close();
        //    }
        //}

        //private static DataTable ReadDt_Estate_NewCases(DateTime someday)
        //{
        //    DataTable dt = null;
        //    Database sourcedb = DB.TaskReader;
        //    string sql = string.Format("select RealEstateID,COUNT(1) as DayNewCaseNum from Cases where AddedTime>='{0}' and AddedTime<='{1}' group by RealEstateID", someday.AddDays(-1.0).ToString("yyyy-MM-dd 00:00"), someday.ToString("yyyy-MM-dd 00:00"));
        //    DbCommand dbCommand = sourcedb.GetSqlStringCommand(sql);
        //    dt = sourcedb.ExecuteDataSet(dbCommand).Tables[0];
        //    return dt;
        //}
        //private static DataTable ReadDt_Case_DayHits(DateTime someday)
        //{
        //    DataTable dt = null;
        //    Database sourcedb = DB.TaskReader;
        //    string sql = string.Format("select ObjectID,COUNT(1) as Hits_Count from [HitsWeiKe] h with(nolock) where TypeID=5 and h.VisitTime>='{0}' and h.VisitTime<'{1}' and ObjectID>0 group by ObjectID;", someday.AddDays(-1.0).ToString("yyyy-MM-dd 00:00"), someday.ToString("yyyy-MM-dd 00:00"));
        //    DbCommand dbCommand = sourcedb.GetSqlStringCommand(sql);
        //    dt = sourcedb.ExecuteDataSet(dbCommand).Tables[0];
        //    return dt;
        //}
        //private static DataTable ReadDt_Estate_CaseListDayHits(DateTime someday)
        //{
        //    DataTable dt = null;
        //    Database sourcedb = DB.TaskReader;
        //    string sql = string.Format("select EstateID,COUNT(1) as Hits_Count from HitsWeiKe_Estate where EstateID>20 and VisitTime>='{0}' and VisitTime<'{1}' group by EstateID", someday.AddDays(-1.0).ToString("yyyy-MM-dd 00:00"), someday.ToString("yyyy-MM-dd 00:00"));
        //    DbCommand dbCommand = sourcedb.GetSqlStringCommand(sql);
        //    dt = sourcedb.ExecuteDataSet(dbCommand).Tables[0];
        //    return dt;
        //}
        //private static DataTable GetMap_Case_Estate()
        //{
        //    DataTable dt = null;
        //    Database sourcedb = DB.TaskReader;
        //    string sql = string.Format("select CaseID,RealEstateID from Cases with(nolock) where RealEstateID>0 and CaseID>0");
        //    DbCommand dbCommand = sourcedb.GetSqlStringCommand(sql);
        //    dt = sourcedb.ExecuteDataSet(dbCommand).Tables[0];
        //    return dt;
        //}
        //private static DataTable PrepareDt_EstateStat(DateTime someday)
        //{
        //    DataTable dtCaseEstate = GetMap_Case_Estate();
        //    DataTable dtCaseInfoHits = ReadDt_Case_DayHits(someday);

        //    var query_CaseEstate_Join_CaseInfoHits
        //        = from e in dtCaseEstate.AsEnumerable()
        //          join h in dtCaseInfoHits.AsEnumerable()
        //          on e.Field<int>("CaseID") equals h.Field<int>("ObjectID") into eh
        //          from item in eh.DefaultIfEmpty()
        //          select new
        //          {
        //              RealEstateID = e.Field<long>("RealEstateID"),
        //              CaseID = e.Field<int>("CaseID"),
        //              Hits_Count = (item == null) ? 0 : item.Field<int>("Hits_Count")
        //          };
        //    var query_Estate_CaseInfoHits
        //        = from p in query_CaseEstate_Join_CaseInfoHits
        //          group p by p.RealEstateID into g
        //          select new
        //          {
        //              EstateID = g.Key,
        //              DayHits = g.Sum(x => x.Hits_Count)
        //          };

        //    DataTable dtEstate4sCaseListHits = ReadDt_Estate_CaseListDayHits(someday);
        //    var query_CaseHits_Merge_CaseListHits
        //        = from e in query_Estate_CaseInfoHits
        //          join h in dtEstate4sCaseListHits.AsEnumerable()
        //          on e.EstateID equals h.Field<long>("EstateID") into eh
        //          from item in eh.DefaultIfEmpty()
        //          select new
        //          {
        //              RealEstateID = e.EstateID,
        //              DayCaseInfoHits = e.DayHits,
        //              DayCaseListHits = (item == null) ? 0 : item.Field<int>("Hits_Count")
        //          };

        //    DataTable dtEstateNewCases = ReadDt_Estate_NewCases(someday);
        //    var query_CaseHits_Merge_CaseListHits_Merge_NewCases
        //        = from e in query_CaseHits_Merge_CaseListHits
        //          join c in dtEstateNewCases.AsEnumerable()
        //          on e.RealEstateID equals c.Field<long>("RealEstateID") into ec
        //          from item in ec.DefaultIfEmpty()
        //          select new
        //          {
        //              RealEstateID = e.RealEstateID,
        //              DayCaseInfoHits = e.DayCaseInfoHits,
        //              DayCaseListHits = e.DayCaseListHits,
        //              DayNewCaseNum = (item == null) ? 0 : item.Field<int>("DayNewCaseNum")
        //          };
        //    var query_FilterNull
        //        = from e in query_CaseHits_Merge_CaseListHits_Merge_NewCases
        //          where e.DayNewCaseNum > 0 || e.DayCaseInfoHits > 0 || e.DayCaseListHits > 0
        //          select new
        //          {
        //              RealEstateID = e.RealEstateID,
        //              DayCaseInfoHits = e.DayCaseInfoHits,
        //              DayCaseListHits = e.DayCaseListHits,
        //              DayNewCaseNum = e.DayNewCaseNum
        //          };

        //    DataTable result = PrepareSchemaUdtEstateStat();
        //    foreach (var item in query_FilterNull)
        //    {
        //        DataRow row = result.NewRow();
        //        row["RealEstateID"] = item.RealEstateID;
        //        row["StatDate"] = someday.AddDays(-1.0);
        //        row["DayCaseInfoHits"] = item.DayCaseInfoHits;
        //        row["DayCaseListHits"] = item.DayCaseListHits;
        //        row["DayNewCaseNum"] = item.DayNewCaseNum;
        //        result.Rows.Add(row);
        //        //    targetRow = targetTable.NewRow();
        //        //targetRow["City"] = dr["CityName"].ToString();
        //        //targetRow["CityID"] = (int)dr["id"];
        //        //targetTable.Rows.Add(targetRow);
        //    }
        //    //query2.ToList().ForEach(result.Rows.Add(result.NewRow()

        //    //DataTable result = ToDataTable(query2);
        //    return result;
        //}
        //public static DataTable PrepareSchemaUdtEstateStat()
        //{
        //    DataTable dt = new DataTable();
        //    dt.Columns.AddRange(new DataColumn[]
        //    { 
        //        new DataColumn("RealEstateID",typeof(long)),
        //        new DataColumn("StatDate",typeof(DateTime)),
        //        new DataColumn("DayNewCaseNum",typeof(int)), 
        //        new DataColumn("DayCaseListHits",typeof(int)), 
        //        new DataColumn("DayCaseListClicks",typeof(int)),
        //        new DataColumn("DayCaseInfoHits",typeof(int)),
        //        new DataColumn("DayCaseInfoClicks",typeof(int))
        //    });
        //    return dt;
        //}

        public static System.Data.DataTable ToDataTable<T>(IEnumerable<T> data) 
        { 
            var dt = new System.Data.DataTable(); 
            var ps = System.ComponentModel.TypeDescriptor.GetProperties(typeof(T));
            foreach (System.ComponentModel.PropertyDescriptor dp in ps) 
                dt.Columns.Add(dp.Name, dp.PropertyType); 
            foreach (T t in data) 
            {
                var dr = dt.NewRow();
                foreach (System.ComponentModel.PropertyDescriptor dp in ps) 
                    dr[dp.Name] = dp.GetValue(t);
                dt.Rows.Add(dr);
            } 
            return dt; 
        } 


        //private static int GetHits4SLastID(NorthSouth ns)
        //{
        //    int result = -1;
        //    string SiteStatusName = Constant.DicNorthSouth[ns].StatusName;
        //    Database db = DB.ZxbReader;
        //    string sql = "SELECT StatusValue from SiteStatus where StatusName=@StatusName";
        //    DbCommand dbCommand = db.GetSqlStringCommand(sql);
        //    db.AddInParameter(dbCommand, "@StatusName", DbType.String, SiteStatusName);
        //    int.TryParse(db.ExecuteScalar(dbCommand).ToString(), out result);
        //    return result;
        //}
    }
}
